<?php
/*
对phpexcel的封装
todo:图片，背景色等
*/
class TMIS_Phpexcel {
  var $arrContent;
  //按行号索引的内容
  var $rowsContent;

  var $objReader;
  var $objPHPExcel;
  var $objActSheet;
  var $objWriter;
  var $maxRowIndex;

  var $type;
  var $extensionName;

  //临时文件保存地址,服务器端需要生成一个临时文件，用来被访问
  var $tempFilePath="./";

  //需要从officeweb365网站上获得，不同的域名对应不同的id
  var $officeId = 19193;

  //预插入内容
  var $preInsertRows=array();

  //预删除内容
  var $preRemoveRows=array();

  //预复制内容
  var $preCopyRows=array();

  //合并单元格合计
  // var $mergeCells =array();

  //传入模板和数据源
  //模板是excel97-2003模板
  function __construct($arr,$tpl) {
    FLEA::org('PHPExcel/PHPExcel.php');

    $this->type=PHPExcel_IOFactory::identify ($tpl);
    $this->extensionName = $this->type=="Excel2007" ? "xlsx" : "xls";

    $this->objReader = PHPExcel_IOFactory::createReader($this->type);
    $this->objPHPExcel = $this->objReader->load($tpl);
    $this->objActSheet = $this->objPHPExcel->getActiveSheet();

    $this->maxRowIndex = $this->objActSheet->getHighestRow();

    // dump($this->mergeCells);exit;

    $this->objWriter = PHPExcel_IOFactory::createWriter( $this->objPHPExcel, $this->type);

    $this->arrContent = $arr;
    if(is_array($this->arrContent)) $this->sort();
  }

  //重新设置数据源
  function setData($data) {
    $this->arrContent = $data;
    if(is_array($this->arrContent)) $this->sort();
  }

  //插入行
  function insertRow($i,$cntRows) {
    //如果i=-1,表示最后一行
    if($i==-1) {
      $i=max(array_keys($this->rowsContent))+$cntRows;
    }

    //插入之后的行号改变
    $temp = array();
    foreach($this->rowsContent as $key=>&$v) {
      if($key>=$i) {
        $temp[$key+$cntRows] = $v;
        unset($this->rowsContent[$key]);
      }
    }

    $this->rowsContent += $temp;
    ksort($this->rowsContent);

    //执行excel的插入语句，修改模板内容
    $this->objActSheet->insertNewRowBefore($i,$cntRows);

    //判断是否需要合并，要考虑被复制行是否处于合并行中，如是需要合并
    //复制目标位置是否处于合并行中，如是，跳过

    //将上一行的合并信息并到复制行中
    $temp = array();
    for($_i=0;$_i<$cntRows;$_i++) {
      $temp[] = $i+$_i;
    }
    $this->copyMerge($i-1,$temp);

    //复制上一行的行高
    $this->copyHeight($i-1,$temp);
    // $pexcel->objActSheet->getRowDimension($insertRow)->setRowHeight($lineHeight1);
    //to do
    return $i;
  }

  //批量插入接口，不会直接插入，在render时才会执行插入
  //$datas表示插入的行中各单元格内容,如 array('A'=>'a','B'=>'b')
  function insertRows($i,$cntRows,$datas) {
    // for($_i=0;$_i<$cntRows;$_i++) {

    // }
    if($cntRows!=count($datas)) {
      echo "insertRows接口的参数不复合要求！";
      exit;
    }
    $this->preInsertRows[$i] = $datas;

  }

  //批量复制行
  //$fromRows,$toRows都是简单数组
  function preCopyRows($options) {
    $fromRows = $options['fromRows'];
    $fromRow = $fromRows[0];
    $lastRow = $fromRows[count($fromRows)-1];
    $datas = $options['datas'];
    $toRows = $options['toRows'];
    //datas中的行数必须和源行保持一致
    if(count($fromRows)!=count($datas)) {
      echo "preCopyRows接口中fromRows的行数必须和数据行保持一致！";
      exit;
    }
    if(empty($toRows)) $options['toRows'] = array($lastRow+1);
    //判断torows是否合法
    foreach($options['toRows'] as &$v) {
      if($v>$fromRow && $v<=$lastRow) {
        echo "不能在被复制行内插入新行！";
        exit;
      }
    }
    // dump($options);exit;
    $this->preCopyRows[] = $options;
  }

  //复制$form行中的合并单元格信息到$to中
  //如果复制行的合并单元格和被复制行的有重叠，跳过 todo
  function copyMerge($fromRow,$toRows) {
    if($fromRow==0) return false;
    if(!$toRows) return false;
    if(!is_array($toRows)) $toRows= array($toRows);
    $mergeCells = $this->objActSheet->getMergeCells();

    //需要合并的列
    $arrColToMerge = array();
    //得到mergeCells中所有存在合并信息的行
    $arrRows = array();
    foreach($mergeCells as $key=>&$v) {
      $temp = explode(':',$v);
      $indexRowFrom = $this->getRowIndex($temp[0]);
      $indexRowTo = $this->getRowIndex($temp[1]);
      for($i=$indexRowFrom;$i<$indexRowTo+1;$i++) {
        $arrRows[$key] = $i;
        if($i==$fromRow) {
          $indexColFrom = $this->getColIndex($temp[0]);
          $indexColTo = $this->getColIndex($temp[1]);
          $arrColToMerge[] = array('from'=>$indexColFrom,'to'=>$indexColTo);
        }
      }
    }

    //开始合并
    $arrPosMerge = array();

    foreach($toRows as $key=>&$v) {
      //如果toRows中本身包含了合并信息，取消合并
      foreach($arrRows as $kk=>&$vv) {
        if($v==$vv) {
          $this->objActSheet->unmergeCells($kk);
        }
      }
      foreach($arrColToMerge as $kk=>&$vv) {
        $arrPosMerge[] = "{$vv['from']}{$v}:{$vv['to']}{$v}";
      }
    }
    // dump($mergeCells);dump($arrRows);dump($arrColToMerge);dump($arrPosMerge);exit;
    foreach($arrPosMerge as $key=>&$v) {
      $this->objActSheet->mergeCells($v);
    }
  }

  //复制行高
  function copyHeight($fromRow,$toRows) {
    if(!is_array($toRows)) $toRows= array($toRows);
    // $pexcel->objActSheet->getRowDimension($insertRow)->setRowHeight($lineHeight1);
    $height = $this->objActSheet->getRowDimension($fromRow)->getRowHeight();
    foreach($toRows as &$v) {
      $this->objActSheet->getRowDimension($v)->setRowHeight($height);
    }
  }



  //从位置信息中获得行号
  function getRowIndex($pos) {
    preg_match('/\d+/',$pos,$arrTemp);
    return $arrTemp[0];
  }

  //从位置信息中获得列号
  function getColIndex($pos) {
    $indexCol=preg_replace("/\\d+/",'', $pos);
    return $indexCol;
  }

  //删除行
  //从第几行开始，删除几行
  function removeRow($i,$cntRows) {
    // dump($this->rowsContent);
    //删除之后的行号改变
    $temp = array();
    foreach($this->rowsContent as $key=>&$v) {
      if($key>=$i) {
        $temp[$key-$cntRows] = $v;
        unset($this->rowsContent[$key]);
      }
    }

    $this->rowsContent += $temp;
    ksort($this->rowsContent);

    // dump($this->rowsContent);exit;
    //执行excel的插入语句，修改模板内容
    $this->objActSheet->removeRow($i,$cntRows);
    // $this->objActSheet->insertNewRowBefore($i,$cntRows);
    return $i;
  }

  //批量删除
  //注意这里是逻辑删除，真实的删除必须等到render时才会执行
  function removeRows($i,$cntRows) {
    // $temp = array();
    // for($ii=0;$ii<$cntRows;$ii++) {
    //   $temp[] = array();
    // }
    $this->preRemoveRows[$i] = array('cnt'=>$cntRows);
  }


  //设置某个单元格的值
  function setCell($pos,$val) {
    // $this->arrContent[$pos] = $val;
    preg_match('/\d+/',$pos,$arr);
    $indexRow = $arr[0];
    $indexCol=preg_replace("/\\d+/",'', $pos);
    $this->rowsContent[$indexRow][] = array(
      'indexCol'=>$indexCol,
      'text'=>$val
    );
  }

  //渲染
  function render($data=null) {
    if($data==null) {
      $temp = array();
      foreach($this->rowsContent as $indexRow=>&$v) {
        foreach($v as $indexCol=>&$vv) {
          $temp[$vv['indexCol'].$indexRow] = $vv['text'];
        }
      }
      $this->arrContent = $temp;
    } else {
      $this->arrContent = $data;
    }


    foreach($this->arrContent as $key=>&$v) {
      $key = strtoupper($key);
      $this->setCellValue($key,$v);

    }

    //开始处理需要插入和需要删除的行
    //合并插入行和删除行

    //构造一个和最终行数对应的数组
    $maxRow = $this->maxRowIndex;
    $arrRows = array();
    $arrSource = array();
    for($i=0;$i<$maxRow+1;$i++) {
      $arrRows[$i]=array('oldPos'=>$i);
      $arrSource[$i]=array('oldPos'=>$i);
    }
    foreach($this->preInsertRows as $key=>&$v) {
      $temp = array();
      foreach($v as $kk=>&$vv) {
        $temp[] = array('oldPos'=>$key-1,'action'=>'insert','data'=>$vv);
      }
      //找到rowindex对应的位置
      foreach($arrRows as $kkk=>&$vvv) {
        if($vvv['oldPos']==$key && !$vvv['action']) {
          array_splice($arrRows, $kkk, 0, $temp);
          break;
        }
      }
    }
    // dump($this->preCopyRows);exit;
    foreach($this->preCopyRows as $key=>&$v) {
      $cntCopy = count($v['fromRows']);
      $temp = array();
      foreach($v['fromRows'] as $kk=>&$vv) {
        $temp[] = array('oldPos'=>$vv,'action'=>'copy','data'=>$v['datas'][$kk]);
      }
      // dump($temp);dump($v);exit;
      foreach($v['toRows'] as &$vv) {
        $rowIndex = $vv;
        //找到rowindex对应的位置
        foreach($arrRows as $kkk=>&$vvv) {
          if($vvv['oldPos']==$rowIndex && !$vvv['action']) {
            array_splice($arrRows, $kkk, 0, $temp);
            break;
          }
        }

      }
    }
    foreach($this->preRemoveRows as $key=>&$v) {
      //找到rowindex对应的位置
      $start =0;
      foreach($arrRows as $kkk=>&$vvv) {
        if($vvv['oldPos']==$key && !$vvv['action']) {
          $vvv['action']='remove';
          $start = $kkk;
          break;
        }
      }
      //往后做标记
      $cnt = 1;
      for($i=$start;$i<count($arrRows);$i++) {
        if($arrRows[$i]['action']!='') continue;
        if($cnt<$v['cnt']) {
          $arrRows[$i]['action']='remove';
          $cnt++;
        }
      }
    }

    // dump($arrRows);dump($rows);exit;

    //首先处理插入问题
    foreach($arrRows as $key=>&$v) {
      if($v['action']=='copy' || $v['action']=='insert') {
        $this->objActSheet->insertNewRowBefore($key,1);
      }
    }

    //复制和插入,赋值
    foreach($arrRows as $key=>&$v) {
      if(!$v['action'] || $v['action']=='remove') continue;
      //得到oldpos对应的最新位置
      foreach($arrRows as $kkk=>&$vvv) {
        if($vvv['oldPos']==$v['oldPos'] && (!$vvv['action'] || $vvv['action']=='remove')) {
          $newPos = $kkk;
        }
      }
      if($v['action']=='copy') {
        $this->copyRowFull($newPos,$key);
        // continue;
      }
      if($v['action']=='insert') {
        $this->copyMerge($newPos,$key);
        $this->copyHeight($newPos,$key);
        // continue;
      }

      if($v['data']) {
        foreach($v['data'] as $kk=>&$vv) {
          $this->setCellValue("{$kk}{$key}",$vv);
        }
      }
    }


    //删除,每删除一行，下次删除的序号需要递减，因为行数变化了。
    $i = 0;
    foreach($arrRows as $key=>&$v) {
      if($v['action']=='remove') {
        //2019年11月20日 by jeff,将下一行的合并信息复制到待删除的行,否则会报错
        // dump("复制".($key-$i+1)."=>".($key-$i));
        $this->copyMerge($key-$i+1,$key-$i);
        $this->objActSheet->removeRow($key-$i,1);
        $i++;
        continue;
      }
    }

  }

  //替换模板变量
  function replaceVar($vars) {
    $arrPos = $this->objActSheet->getCellCollection();
    $temp = array();
    $tempRows = array();
    foreach($arrPos as &$pos) {
      $cellValue = $this->objActSheet->getCell($pos)->getValue();
      if(empty($cellValue)) continue;
      preg_match_all('/{([\s\S]*?)}/', $cellValue, $arrTemp);
      if(count($arrTemp[1])==0) continue;
      //如果是数组，另外赋值
      //:号在{}里面的才算，外面的不算
      $_pos = strpos($cellValue, ':');
      $_pos2 = strpos($cellValue, '{');
      $_pos3 = strpos($cellValue, '}');
      if($_pos < $_pos2 || $_pos > $_pos3){
        $_pos = false;
      }
      if($_pos!==false) {
      // dump($cellValue);dump('位置'.strpos($cellValue, ':'));
        $key = substr($cellValue,1,$_pos-1);
        $col = substr($cellValue,$_pos+1,-1);
        // dump($col);exit;
        $tempRows[$key][$this->getRowIndex($pos)][$this->getColIndex($pos)] = $col;
        // $this->objActSheet->setCellValue($pos,$vars);
        continue;
      }
      foreach($arrTemp[1] as &$v) {
        $cellValue = str_replace('{'.$v.'}', $vars[$v], $cellValue);
      }
      $this->objActSheet->setCellValue($pos,$cellValue);
    }

    foreach($tempRows as $key=>&$v) {
      ksort($v);
      $i =0;
      foreach($v as $row=>&$vv) {
        foreach($vv as $col=>&$colName) {
          // dump($col.$row);dump($colName);dump($vars[$key][$i]);
          $this->objActSheet->setCellValue($col.$row,$vars[$key][$i][$colName]);
        }
        $i++;
      }
    }
    // dump($tempRows);exit;
  }

  //将内容按行排序
  function sort() {
    $c = $this->arrContent;
    $temp = array();
    foreach($c as $key=>&$v) {
      preg_match('/\d+/',$key,$arr);
      $indexRow = $arr[0];
      $indexCol=preg_replace("/\\d+/",'', $key);
      $temp[$indexRow][] = array(
        'indexCol'=>$indexCol,
        'text'=>$v
      );
    }
    ksort($temp);
    $this->rowsContent = $temp;
  }

  //复制整行内容，合并单元格会取消
  //注意，复制的内容必须是模板中原先存在的内容，如果是代码中复制的单元格内容，不会复制成功。
  function copyRowFull($row_from, $row_to) {
    $ws_from = & $this->objActSheet;
    $ws_to = & $this->objActSheet;
    $ws_to->getRowDimension($row_to)->setRowHeight($ws_from->getRowDimension($row_from)->getRowHeight());
    $lastColumn = $ws_from->getHighestColumn();
    ++$lastColumn;
    for ($c = 'A'; $c != $lastColumn; ++$c) {
      $cell_from = $ws_from->getCell($c.$row_from);
      $cell_to = $ws_to->getCell($c.$row_to);
      $cell_to->setXfIndex($cell_from->getXfIndex()); // black magic here
      $cell_to->setValue($cell_from->getValue());
    }

    $this->copyMerge($row_from, $row_to);
  }

  //导出
  function export($filename) {
    // $filename ='exportTest';
    ob_end_clean();//清除缓存 防止乱码
    header ('Content-Type: application/vnd.ms-excel');
    header ("Content-Disposition: attachment;filename=\"{$filename}.{$this->extensionName}\"" ); //"'.$filename.'.xls"
    header ('Cache-Control: max-age=0');
    header("Content-Type:application/download");;
    header("Content-Transfer-Encoding:binary");
    // $objWriter = PHPExcel_IOFactory::createWriter ( $objPHPExcel, 'Excel5' ); //在内存中准备一个excel2003文件
    $this->objWriter->save ( 'php://output' );
  }

  //使用365web预览
  function preview() {
    // FLEA::loadClass('TMIS_Phpexcel');
    // $pexcel = &new TMIS_Phpexcel(array(),'Lib/App/Template/IndexVue/Test/test.xlsx');
    $urlFile = $this->save2qiniu();
    $url = $this->getUrlPreview($urlFile);
    js_alert(null,null,$url);
  }

  //获得单元格样式
  function getStyle($pos) {
    return $this->objActSheet->getStyle($pos);
  }

  //设置单元格样式
  function setStyle($style,$posRange) {
    $this->objActSheet->duplicateStyle($style,$posRange);
  }

  //切换sheet
  function getSheet($index) {
    $this->objActSheet = $this->objPHPExcel->setActiveSheetIndex($index);
    //清空数据
    $this->arrContent = array();
    $this->rowsContent = array();
  }

  //直接调用原生方法
  function setCellValue($pos,$value) {
    $this->objActSheet->setCellValue($pos,$value);
  }

  //插入图片
  //$arr['keepProp'] 是否按比例缩放,默认按比例
  function setCellPic($pos,$arr) {
    $pos = strtoupper($pos);
    $height = $arr['height'];
    $width = $arr['width'];
    $path = $arr['path'];
    //默认不按比例缩放，
    $keepProp= isset($arr['keepProp'])?$arr['keepProp']:true;

    //判断文件是否存在
    if(empty($path)) return true;
    if(!file_exists($path)) {
      echo "图片文件不存在:{$path}";
      exit;
    }
    $objDrawing = new PHPExcel_Worksheet_Drawing();
    /*设置图片路径*/
    $objDrawing->setPath($path);
    //设置不按比例缩放
    $objDrawing->setResizeProportional($keepProp);
    /*设置图片高度*/
    $height && $objDrawing->setHeight($height);
    $width && $objDrawing->setWidth($width);
    /*设置图片要插入的单元格*/
    $objDrawing->setCoordinates($pos);
    /*设置图片所在单元格的格式*/
    $objDrawing->setOffsetX(5);
    $objDrawing->setOffsetY(5);
    $objDrawing->getShadow()->setVisible(true);
    $objDrawing->getShadow()->setDirection(50);
    $objDrawing->setWorksheet($this->objActSheet);
  }

  //保存excel文件到七牛空间
  function save2qiniu() {
    $fileName = "{$_SESSION['USERNAME']}_PHPEXCEL_".date("ymdHis").".".$this->extensionName;
    // //测试用，已经上传道192.168.1.170上了,后期会考虑上传到七牛云上
    $filePath = $this->tempFilePath.$fileName;
    $this->objWriter->save($filePath);

    //上传到七牛空间
    FLEA::loadClass('TMIS_Qiniu');
    $objQiniu = new TMIS_Qiniu();
    $urlFile = $objQiniu->upload($fileName);
    unlink($filePath);
    return $urlFile;
  }

  //得到预览地址
  function getUrlPreview($url) {
    return "http://ow365.cn/?i=19193&furl={$url}";
  }

  //设置行高为0
  function setRowHeightZero($num,$toNum='') {
    if($toNum){
      for ($i=$num; $i<=$toNum; $i++) {
        $this->objActSheet->getRowDimension($i)->setRowHeight(0.1);
      }
    }else{
      $this->objActSheet->getRowDimension($num)->setRowHeight(0.1);
    }
  }

}